package com.toppitch.site.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.toppitch.site.config.AppConfig;

@Component
public class DBManager {
	private static final String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	private static final String uri = "jdbc:derby:{DB_NAME};create=true";

	@Autowired
	private AppConfig appConfig = null;

	private Connection con = null;

	@PostConstruct
	public void init() {
		String dburi = uri.replace("{DB_NAME}", appConfig.getRoot()
				+ appConfig.getMap().get("DB_NAME"));

		try {
			Class.forName(driver);
			con = DriverManager.getConnection(dburi);

			//createTables();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
//		alterStudentInvestingBackground();
//		alterFileNames();
	}

	protected void alterStudentInvestingBackground() {
		String sql = "alter table student alter column investingBackground set data type varchar(8000)";
		execute(sql);
	}
	
	protected void alterFileNames() {
		execute("alter table photo alter column photoFileName set data type varchar(255)");
		execute("alter table model alter column modelFileName set data type varchar(255)");
		execute("alter table pitch alter column modelFileName set data type varchar(255)");
	}

	protected void createTables() {
		Statement st = null;
		try {
			st = con.createStatement();
			String sql = "create table student(" + //
					"firstName varchar(255)," + //
					"lastName varchar(255)," + //
					"email varchar(255)," + //
					"password varchar(255)," + //
					"college varchar(255)," + //
					"graduateSchool varchar(255)," + //
					"yearOfGraduation varchar(255)," + //
					"roleOfInterest varchar(255)," + //
					"locationPreference varchar(255)," + //
					"currentEmployer varchar(255)," + //
					"jobTitle varchar(255)," + //
					"formerEmployer varchar(255)," + //
					"employerIndustry varchar(255)," + //
					"availability varchar(255)," + //
					"investingBackground varchar(8000)," + //
					"cvId varchar(35)," + //
					"cvFileName varchar(255)," + //
					"photoId varchar(35)," + //
					"pitchCount int," + //
					"modelCount int" + //
					")";
			st.execute(sql);
			sql = "create table cv(" + //
					"cvId varchar(35)," + //
					"cvFileName varchar(255)" + //
					")";
			st.execute(sql);
			sql = "create table photo(" + //
					"photoId varchar(35)," + //
					"photoFileName varchar(255)" + //
					")";
			st.execute(sql);
			sql = "create table pitch(" + //
					"pitchId varchar(35)," + //
					"companyTicker varchar(35)," + //
					"companyName varchar(35)," + //
					"currentPrice varchar(35)," + //
					"targetPrice varchar(35)," + //
					"assetClass varchar(35)," + //
					"geography varchar(35)," + //
					"recommendation varchar(35)," + //
					"country varchar(35)," + //
					"expectedTimeframe varchar(35)," + //
					"situation varchar(35)," + //
					"sector varchar(35)," + //
					"ideaAvailability varchar(35)," + //
					"thesisId varchar(35)," + //
					"variantViewId varchar(35)," + //
					"modelId varchar(35)," + //
					"modelType varchar(35)," + //
					"modelFileName varchar(255)," + //
					"description varchar(8000)," + //
					"authorEmail varchar(255)," + //
					"authorFullName varchar(255)," + //
					"posttime bigint," + //
					"commentCount int" + //
					")";
			st.execute(sql);
			sql = "create table comment(" + //
					"commentId varchar(35)," + //
					"pitchId varchar(35)," + //
					"authorEmail varchar(255)," + //
					"postTime bigint," + //
					"authorHF varchar(10)," + //
					"authorPhotoId varchar(35)," + //
					"authorName varchar(511)" + //
					")";
			System.out.println(sql);
			st.execute(sql);
			sql = "create table model(" + //
					"modelId varchar(35)," + //
					"modelFileName varchar(255)" + //
					")";
			st.execute(sql);
			sql = "create table hf(" + //
					"name varchar(255)," + //
					"email varchar(255)," + //
					"password varchar(255)," + //
					"photoId varchar(35)" + //
					")";
			st.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		execute("create table careerinterest(" + //
				"careerInterest varchar(255)," + //
				" studentEmail varchar(255))");
	}

	public boolean execute(String sql) {
		Statement st = null;
		try {
			st = con.createStatement();
			st.execute(sql);
			return true;
		} catch (SQLException e) {
			System.out.println(sql);
			e.printStackTrace();
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return false;
	}

	public <T extends IGetFromResultSet> void queryFirst(T t, String sqltemp,
			String... args) {
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			st = con.prepareStatement(sqltemp);
			for (int i = 0; i < args.length; ++i) {
				st.setString(i + 1, args[i]);
			}
			rs = st.executeQuery();
			if (rs.next()) {
				t.getFromResultSet(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public void doQuery(IGetFromResultSet callback, String sql, Object... args) {
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			st = con.prepareStatement(sql);
			for (int i = 0; i < args.length; ++i) {
				if (args[i] instanceof String) {
					st.setString(i + 1, (String) args[i]);
				} else if (args[i] instanceof Integer) {
					st.setInt(i + 1, (Integer) args[i]);
				}
			}
			rs = st.executeQuery();
			callback.getFromResultSet(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	public String management(String sql, boolean query){
		if(!query){
			return "" + execute(sql);
		}
		final StringBuilder sb = new StringBuilder();
		doQuery(new IGetFromResultSet(){
			public void getFromResultSet(ResultSet rs) throws SQLException {
				ResultSetMetaData resultMetaData = rs.getMetaData();
				for(int i = 0; i < resultMetaData.getColumnCount(); ++i){
					sb.append(resultMetaData.getColumnLabel(i + 1)).append("\t");
				}
				while(rs.next()){
					sb.append("\r\n");
					for(int i = 0; i < resultMetaData.getColumnCount(); ++i){
						sb.append(rs.getObject(i + 1)).append("\t");
					
					}
				}
			}
		}, sql);
		
		return sb.toString();
	}

	public void test() throws SQLException {
		String sql = "create table student(firstname varchar(35), lastname varchar(35))";
		Statement st = con.createStatement();
		st.execute(sql);

		sql = "insert into student values('first name 1', 'last name 1')";
		st.execute(sql);

		sql = "select * from ( select row_number() over() as rownum, student.* from student) as tmp where rownum <= 5";
		ResultSet rs = st.executeQuery(sql);
		rs.getMetaData();
		while (rs.next()) {
			System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>"
					+ rs.getString("firstname"));
			System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>"
					+ rs.getString("lastname"));
		}

		sql = "drop table student";
		st.execute(sql);
	}

	@PreDestroy
	public void close() {
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			con = null;
		}
	}
}
